from collections import defaultdict
from openpyxl import load_workbook
from config import recharge_class, config_data, delete_empty_rows, create_excel

LEVEL_1 = '优'
LEVEL_2 = '良'
LEVEL_3 = '中'
LEVEL_4 = '差'
LEVEL_5 = '未交'

def read_data(filenames):
    result = defaultdict(list)
    for filename in filenames:
        wb = load_workbook(filename)
        sheet = wb['作业统计']
        max_row = sheet.max_row
        for i in range(5, max_row+1):
            _name = sheet.cell(i,1).value
            _id =  sheet.cell(i,2).value
            _class =  sheet.cell(i,6).value
            if recharge_class:
                if _class in recharge_class:
                    teach_class = recharge_class.get(_class)
                else:
                    teach_class = recharge_class.get('other')
            else:
                teach_class = _class
            levels = []
            for j in range(6, sheet.max_column + 1, 3):
                _score =  sheet.cell(i,j+1).value
                if not _score:
                    _score = -1
                _isSubmit =  sheet.cell(i,j+2).value
                if _isSubmit and _isSubmit == '未完成':
                    _score = -1
                level = LEVEL_5
                if 100 >= _score >= 80:
                    level = LEVEL_1
                elif 79 >= _score >= 60:
                    level = LEVEL_2
                elif 59 >= _score >= 30:
                    level = LEVEL_3
                elif 0 >= _score >=29:
                    level = LEVEL_4
                levels.append(level)
            if teach_class in result:
                result[teach_class].append((_id, _name, levels))
            else:
                result[teach_class] = [(_id,_name, levels)]
    return result

def write_data(file,_class, data): # data is a list with tuple
    workbook = load_workbook(file)
    sheet = workbook['作业成绩']
    sheet.cell(1, 1).value = sheet.cell(1, 1).value.replace('semester', config_data['semester'])
    sheet.cell(2, 1).value = (sheet.cell(2, 1).value
                              .replace('class_name', _class)
                              .replace('course_name', config_data['course_name'])
                              .replace('teacher_name', config_data['teacher_name']))
    start_row = 4
    for i in range(len(data)):
        entry = data[i]
        row = start_row + i
        sheet.cell(row, 1).value = i + 1   #序号
        sheet.cell(row, 2).value = str(entry[0]).replace('.0','')  #学号
        sheet.cell(row, 3).value = entry[1]   #姓名
        start_column = 4
        levels = entry[-1]
        for j in range(len(levels)):   # 作业分数列表
            level = levels[j]
            sheet.cell(row, start_column + j).value = level
        formula = '100-X{}*1-Y{}*3-Z{}*5-AA{}*10'.format(row, row, row, row)
        formula = '=IF(({})<0,0,{})'.format(formula, formula)
        sheet.cell(row, 21).value = formula
    delete_empty_rows(sheet, start_row)
    workbook.save(filename=file)

def do_task_homework():
    result = read_data(config_data['read_file_path'])
    for _class, value in result.items():
        filename = create_excel(config_data['output_path'].format(_class))
        write_data(filename, _class, value)


if __name__ == '__main__':
    do_task_homework()
